﻿/*
	Date	: 10.10.11
	Author	: Israel
	Purpose	: Check in form manager
	
 */
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;


namespace GoldAir_DesktopApp
{
    public partial class frmCheckin : Form
    {
        public frmCheckin()
        {
            InitializeComponent();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (txtFlightNumber.Text != "" && txtUserId.Text != "")
            {

                using (DataTable dt = LookupFlight(Int32.Parse(txtUserId.Text), Int32.Parse(txtFlightNumber.Text))) 
                {
                    if (dt.Rows.Count == 0)
                    {
                        txtUserId.Focus();
                        MessageBox.Show("Not Found.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);

                        ClearForm();                       
                        return;
                    }
                    else
                    {
                        //assign the data to the controls
                        
                        txtFrom.Text        = Convert.ToString(dt.Rows[0][0]);  //from
                        txtTo.Text          = Convert.ToString(dt.Rows[0][1]); //to
                        txtFlightTime.Text  = Convert.ToString(dt.Rows[0][2]); //flight time
                        txtSeatNum.Text     = Convert.ToString(dt.Rows[0][3]); //seat n

                        //If its checked in
                        if (Convert.ToString(dt.Rows[0][4]) == "True")
                        {
                            chkCheckIn.Checked = true; //check in
                            txtCheckinDate.Text = Convert.ToString(dt.Rows[0][5]); //checkin date
                        }

                        
                    }
                }
            }
            else
            {
                MessageBox.Show("Enter User Id and Flight Id", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                ClearForm();
            }

        }

        //Lookup flight function
        private static DataTable LookupFlight(int UserId, int FlightNumber)
        {
            
            const string connStr = "server=localhost;User Id=goldair_dapp;password=geckodev;database=goldair_db";
            const string query = "Select route.from,route.destination,flight.departure,user_flight.num_seat,user_flight.checked_in,user_flight.checkin_datetime  from user_flight join flight on user_flight.flight_id=flight.id join route on flight.route_id=route.id Where user_id = @UserId and flight_id = @FlightNumber";

            MySqlConnection connection = new MySqlConnection();
            MySqlDataAdapter data = new MySqlDataAdapter();

            connection.ConnectionString = connStr;
            connection.Open();
            MySqlCommand command = connection.CreateCommand();


            DataTable result = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(query, conn))
                {
                    cmd.Parameters.Add("@UserId", MySqlDbType.Int32).Value = UserId;
                    cmd.Parameters.Add("@FlightNumber", MySqlDbType.Int32).Value = FlightNumber;

                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);
                    }
                }
                conn.Close();
            }
            
            connection.Close();        

            return result;
        }

        private void chkCheckIn_CheckedChanged(object sender, EventArgs e)
        {
            if (chkCheckIn.Checked)
            {
                txtCheckinDate.Text = DateTime.Now.ToString();
            }
            else 
            {
                txtCheckinDate.Text = null;
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (txtFrom.Text != "" && txtSeatNum.Text !="0")
            {
                
                Boolean CheckIn = Convert.ToBoolean(chkCheckIn.Checked);
                DateTime CheckDateTime=DateTime.MinValue;
                Int16 Confirmed = 0;
                if (txtCheckinDate.Text != "")
                {
                     CheckDateTime = Convert.ToDateTime(txtCheckinDate.Text);
                     Confirmed = 1;
                }

                
                const string connStr = "server=localhost;User Id=goldair_dapp;password=geckodev;database=goldair_db";
                const string query = "Update user_flight set checked_in=@CheckIn, checkin_datetime=@CheckDateTime, confirmed=@Confirmed Where user_id = @UserId and flight_id = @FlightNumber";

                MySqlConnection connection = new MySqlConnection();
                MySqlDataAdapter data = new MySqlDataAdapter();

                connection.ConnectionString = connStr;
                connection.Open();
                MySqlCommand command = connection.CreateCommand();

                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(query, conn))
                    {
                        cmd.Parameters.Add("@UserId", MySqlDbType.Int32).Value = txtUserId.Text;
                        cmd.Parameters.Add("@FlightNumber", MySqlDbType.Int32).Value = txtFlightNumber.Text;
                        cmd.Parameters.Add("@CheckIn", MySqlDbType.Int16).Value = CheckIn;
                        cmd.Parameters.Add("@CheckDateTime", MySqlDbType.DateTime).Value = CheckDateTime;
                        cmd.Parameters.Add("@Confirmed", MySqlDbType.Int16).Value = Confirmed;

                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                MessageBox.Show("Check In Saved!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                connection.Close();
            }
            else
            {
                MessageBox.Show("No Data or No Seat Number!",this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
       
        private void ClearForm()
        {

            txtFrom.Text = "";
            txtTo.Text = "";
            txtFlightTime.Text = "";
            txtSeatNum.Text = "";
            txtCheckinDate.Text = "";
            chkCheckIn.Checked = false;

            txtUserId.Focus();
            
        }


    }
}
